#! /bin/bash
HIVE_HOME=/usr/bin/hive
Last_DATE=$(date -d "-1 day" +%Y-%m-%d)
${HIVE_HOME} -S -e "
INSERT INTO zz15_dws_renxinlei.class_leave partition(yearinfo,monthinfo,dayinfo)

SELECT
    class_date AS dateinfo, 
    class_id,
    sum(morning_leave_count) AS morning_leave_count,
    sum(afternoon_leave_count) AS afternoon_leave_count,
    sum(evening_leave_count) AS evening_leave_count,
    substr(class_date,1,4) AS yearinfo,
    substr(class_date,6,2) AS monthinfo,
    substr(class_date,9,2) AS dayinfo
FROM (
SELECT
        ctu.class_date,
        ctu.class_id,
        count(DISTINCT sl.student_id) AS morning_leave_count,
        null AS afternoon_leave_count,
        null AS evening_leave_count
    FROM (SELECT * FROM itcast_ods.student_leave_apply_ods WHERE audit_state = 1 AND cancel_state = 0 AND valid_state = 1) AS sl --audit_state 是否批假，cancel_state 是否取消，valid_state  是否生效
        JOIN itcast_ods.class_time AS ct ON sl.class_id = ct.class_id
        JOIN (SELECT * FROM itcast_ods.course_table_upload_detail WHERE nvl(content,'')!='' and content !='开班典礼') AS ctu ON sl.class_id = ctu.class_id
        WHERE ctu.class_date BETWEEN ct.use_begin_date AND ct.use_end_date
            AND concat(ctu.class_date,' ',ct.morning_begin_time)  >= sl.begin_time
            AND concat(ctu.class_date,' ',ct.morning_begin_time) <= sl.end_time
    GROUP BY ctu.class_date,ctu.class_id
    UNION ALL
    SELECT
        ctu.class_date,
        ctu.class_id,
        null AS morning_leave_count,
        count(DISTINCT sl.student_id) AS afternoon_leave_count,
        null AS evening_leave_count
    FROM (SELECT * FROM itcast_ods.student_leave_apply_ods WHERE audit_state = 1 AND cancel_state = 0 AND valid_state = 1) AS sl --audit_state 是否批假，cancel_state 是否取消，valid_state  是否生效
        JOIN itcast_ods.class_time AS ct ON sl.class_id = ct.class_id
        JOIN (SELECT * FROM itcast_ods.course_table_upload_detail WHERE nvl(content,'')!='' and content !='开班典礼' AND Last_DATE=${Last_DATE}) AS ctu ON sl.class_id = ctu.class_id
        WHERE ctu.class_date BETWEEN ct.use_begin_date AND ct.use_end_date
            AND concat(ctu.class_date,' ',ct.afternoon_begin_time)  >= sl.begin_time
            AND concat(ctu.class_date,' ',ct.afternoon_begin_time) <= sl.end_time
    GROUP BY ctu.class_date,ctu.class_id
    UNION ALL
    SELECT
        ctu.class_date,
        ctu.class_id,
        null AS morning_leave_count,
        null AS afternoon_leave_count,
        count(DISTINCT sl.student_id) AS evening_leave_count
    FROM (SELECT * FROM itcast_ods.student_leave_apply_ods WHERE audit_state = 1 AND cancel_state = 0 AND valid_state = 1) AS sl --audit_state 是否批假，cancel_state 是否取消，valid_state  是否生效
        JOIN itcast_ods.class_time AS ct ON sl.class_id = ct.class_id
        JOIN (SELECT * FROM itcast_ods.course_table_upload_detail WHERE nvl(content,'')!='' and content !='开班典礼') AS ctu ON sl.class_id = ctu.class_id
        WHERE ctu.class_date BETWEEN ct.use_begin_date AND ct.use_end_date
            AND concat(ctu.class_date,' ',ct.evening_begin_time)  >= sl.begin_time
            AND concat(ctu.class_date,' ',ct.evening_begin_time) <= sl.end_time
    GROUP BY ctu.class_date,ctu.class_id
)AS tmp
GROUP BY tmp.dateinfo,class_id